/******************************************************************************
Paper: The Impact of Welfare on Intergroup Relations
Author: Akshay Dixit

CPHS: This .do files produces: 
	- Table S8 (Proportion of households borrowing from relatives or friends)
	- Table S9 (Income and consumption within castes)
	- Descriptive stats on loans by within-caste income quintile, reported in the 
	"Caste and Social Insurance" section.
******************************************************************************/

clear all

gl data_cmie "$identity/data/cphs"

u "$data_cmie/merged_data_clean.dta", clear

* Keep only the Telangana sample
keep if telangana == 1

******************************************************************************

*** Loans from friends/relatives by purpose, pre-treatment ***

	/*
		The analysis uses 6 waves of the CPHS data. 
		Three waves (11, 12, 13) are pre-treatment.
		These waves correspond to May-Aug 2017, Sep-Dec 2017, and Jan-Apr 2018.
	*/

	
	// Define pre-treatment binary variables
foreach i in borrowed_rel_friends_co borrowed_rel_friends_de borrowed_rel_friends_me borrowed_rel_friends_we borrowed_rel_friends_bu borrowed_rel_friends_ho borrowed_rel_friends_ed borrowed_rel_friends_ve borrowed_rel_friends_in {
	g `i' = (`i'_11 == 1 | `i'_12 == 1 | `i'_13 == 1)
	replace `i' = . if (`i'_11 == . & `i'_12 == . & `i'_13 == .)
}

foreach i in has_outstanding_borrowing borrowed_rel_friends borrowed_money_lender borrowed_bank borrowed_shops {
	g `i' = (`i'_11 == 1 | `i'_12 == 1 | `i'_13 == 1)
	replace `i' = . if (`i'_11 == . & `i'_12 == . & `i'_13 == .)
}

	// Create table 
putexcel set "$analysis/caste_borrowings_purpose.xlsx", replace

putexcel B2=("All") C2=("Forward Caste") D2=("OBC") E2=("SC") F2=("ST")
putexcel B1=("(1)") C1=("(2)") D1=("(3)") E1=("(4)") F1=("(5)")
putexcel A3=("Purpose of borrowing") A4=("Any purpose") A5=("Consumption") A6=("Debt repayment") A7=("Medical expenses") A8=("Wedding") A9=("Business") A10=("Investment") A11=("Education") A12=("Housing") A13=("Vehicle")

count
putexcel B15=(r(N))
count if caste_category_nonmissing2 == "Forward Caste"
putexcel C15=(r(N))
count if caste_category_nonmissing2 == "OBC"
putexcel D15=(r(N))
count if caste_category_nonmissing2 == "SC"
putexcel E15=(r(N))
count if caste_category_nonmissing2 == "ST"
putexcel F15=(r(N))

local borrow borrowed_rel_friends borrowed_rel_friends_co borrowed_rel_friends_de borrowed_rel_friends_me borrowed_rel_friends_we borrowed_rel_friends_bu borrowed_rel_friends_in borrowed_rel_friends_ed borrowed_rel_friends_ho borrowed_rel_friends_ve

local row=4

foreach var of local borrow {
	
	sum `var'
	putexcel B`row' = (r(mean))
	
	sum `var' if caste_category_nonmissing2 == "Forward Caste"
	putexcel C`row' = (r(mean))
	
	sum `var' if caste_category_nonmissing2 == "OBC"
	putexcel D`row' = (r(mean))
	
	sum `var' if caste_category_nonmissing2 == "SC"
	putexcel E`row' = (r(mean))
	
	sum `var' if caste_category_nonmissing2 == "ST"
	putexcel F`row' = (r(mean))
	
	local ++row
	
}

******************************************************************************

*** Relative income and consumption within castes ***

	// Keep only castes with at least 30 observations, to parallel Munshi and Rosenzweig (2016)
bysort caste: g caste_n = _N
keep if caste_n >= 30
drop if caste == "Other OBC" | caste == "Other ST" | caste == "Caste not stated" | caste == "Other SC"

	// Within-caste quintiles of income, excluding transfers
foreach i in may17 jun17 jul17 aug17 sep17 oct17 nov17 dec17 jan18 feb18 mar18 apr18 {
	g income_excl_transfers_`i' = total_income_`i' - income_household_governm_`i' - income_household_private_`i'
}
	
egen income_excl_transfers = rowtotal(income_excl_transfers_*), missing
egen income_excl_transfers_quintile = xtile(income_excl_transfers), by(caste) nq(5)

	// Consumption by income quintile
egen total_consumption = rowtotal(total_expenditure_may17 total_expenditure_jun17 total_expenditure_jul17 total_expenditure_aug17 total_expenditure_sep17 total_expenditure_oct17 total_expenditure_nov17 total_expenditure_dec17 total_expenditure_jan18 total_expenditure_feb18 total_expenditure_mar18 total_expenditure_apr18), missing

foreach i in 1 2 3 4 5 {
	summ income_excl_transfers if income_excl_transfers_quintile == `i'
	local mean_income_`i' = (r(mean))
	
	summ total_consumption if income_excl_transfers_quintile == `i'
	local mean_consumption_`i' = (r(mean))
}

putexcel set "$analysis/redistribution_within_castes.xlsx", replace
putexcel B1 = "(1)" C1 = "(2)" D1 = "(3)" 
putexcel B2 = "Relative income" C2 = "Relative consumption" D2 = "Consumption-income ratio"
putexcel A3 = "Relative income class"  A4 = "1" A5 = "2" A6 = "3" A7 = "4" A8 = "5"
putexcel A10 = "Number of households" A11 = "Number of castes"

putexcel B4 = (`mean_income_1'/`mean_income_5')
putexcel B5 = (`mean_income_2'/`mean_income_5')
putexcel B6 = (`mean_income_3'/`mean_income_5')
putexcel B7 = (`mean_income_4'/`mean_income_5')
putexcel B8 = (`mean_income_5'/`mean_income_5')

putexcel C4 = (`mean_consumption_1'/`mean_consumption_5')
putexcel C5 = (`mean_consumption_2'/`mean_consumption_5')
putexcel C6 = (`mean_consumption_3'/`mean_consumption_5')
putexcel C7 = (`mean_consumption_4'/`mean_consumption_5')
putexcel C8 = (`mean_consumption_5'/`mean_consumption_5')

distinct caste

putexcel B10 = `r(N)'
putexcel B11 = `r(ndistinct)'


* Loans by quintile

tab income_excl_transfers_quintile, summ(borrowed_rel_friends)
tab income_excl_transfers_quintile, summ(borrowed_rel_friends_co)

******************************************************************************

clear
